package MyDaily.dao.daily;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import MyDaily.dao.DBUtils;
import MyDaily.dao.DBconnection;
import MyDaily.pojo.Daily;
import MyDaily.pojo.Page;

/*类说明:登录界面的查询方法*/
//统计总数
public class QuertDaily {
	public static int DailyCont(String name) {
		String sql = "select count(*) from diary WHERE name = '" + name + "' ;";
		ResultSet rs = DBUtils.queryData(sql);
		try {
			while (rs.next()) {
				int count = rs.getInt(1);
				return count;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public static int Dailytext(String text) {
		String sql = "select COUNT(*) from diary WHERE diary LIKE '%" + text + "%' ";
		ResultSet rs = DBUtils.queryData(sql);
		try {
			while (rs.next()) {
				int count = rs.getInt(1);
				return count;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	// 模糊查询
	public static List<Daily> Dailydim(String text, int startIndex, int pageSize) {
		// 创建Connection
		Connection conn = null;
		// 创建PreparedStatement
		PreparedStatement pStat = null;
		// 创建ResultSet
		ResultSet rSet = null;
		// 创建Lsit集合储存rs结果集
		List<Daily> dailies = new ArrayList<Daily>();
		try {
			// 定义sql语句
			String sql = "select * from diary WHERE diary LIKE '%" + text + "%' LIMIT " + startIndex + "," + pageSize
					+ "";
			// 获取连接
			conn = DBconnection.getConnection();
			// 编译sql
			pStat = conn.prepareStatement(sql);
			// 执行sql，将查询结果放在结果集中
			rSet = pStat.executeQuery();
			// 判断是否有数据
			while (rSet.next()) {
				// 初始化实体类，并将查询结果添加user属性中
				Daily daily = new Daily();
				daily.setId(rSet.getInt("id"));
				daily.setName(rSet.getString("name"));
				daily.setDiary(rSet.getString("diary"));
				daily.setType(rSet.getString("type"));
				daily.setDate(rSet.getTimestamp("date"));
				;
				// 将实体类放在userlist集合中
				dailies.add(daily);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			// 关闭资源
			try {
				if (pStat != null) {
					pStat.close();
				}

				if (conn != null) {
					conn.close();
				}
				if (rSet != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return dailies;
	}

	// 分页
	public static List<Daily> queDailies(String name, int startIndex, int pageSize) {
		// 创建Connection
		Connection conn = null;
		// 创建PreparedStatement
		PreparedStatement pStat = null;
		// 创建ResultSet
		ResultSet rSet = null;
		// 创建Lsit集合储存rs结果集
		List<Daily> dailies = new ArrayList<Daily>();
		try {
			// 定义sql语句
			String sql = "SELECT * FROM diary WHERE name = '" + name + "' LIMIT " + startIndex + "," + pageSize + "";
			// 获取连接
			conn = DBconnection.getConnection();
			// 编译sql
			pStat = conn.prepareStatement(sql);
			// 执行sql，将查询结果放在结果集中
			rSet = pStat.executeQuery();
			// 判断是否有数据
			while (rSet.next()) {
				// 初始化实体类，并将查询结果添加user属性中
				Daily daily = new Daily();
				daily.setId(rSet.getInt("id"));
				daily.setName(rSet.getString("name"));
				daily.setDiary(rSet.getString("diary"));
				daily.setType(rSet.getString("type"));
				daily.setDate(rSet.getTimestamp("date"));
				;
				// 将实体类放在userlist集合中
				dailies.add(daily);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			// 关闭资源
			try {
				if (pStat != null) {
					pStat.close();
				}

				if (conn != null) {
					conn.close();
				}
				if (rSet != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return dailies;
	}

	// 获取日期查询的总数
	public static int dateCont(String name, Date date) {
		String sql = "select count(*) from diary WHERE name = '" + name + "' and date > '" + date + "'";
		ResultSet rs = DBUtils.queryData(sql);
		try {
			while (rs.next()) {
				int count = rs.getInt(1);
				return count;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	// 按照日期分页查询
	public static List<Daily> DailyDate(String name, Date date, int startIndex, int pageSize) {
		// 创建Connection
		Connection conn = null;
		// 创建PreparedStatement
		PreparedStatement pStat = null;
		// 创建ResultSet
		ResultSet rSet = null;
		// 创建Lsit集合储存rs结果集
		List<Daily> dailies = new ArrayList<Daily>();
		try {
			// 定义sql语句
			String sql = "select * from diary where name= '" + name + "' and date > '" + date + "'LIMIT " + startIndex
					+ "," + pageSize + "";
			// 获取连接
			conn = DBconnection.getConnection();
			// 编译sql
			pStat = conn.prepareStatement(sql);
			// 执行sql，将查询结果放在结果集中
			rSet = pStat.executeQuery();
			// 判断是否有数据
			while (rSet.next()) {
				// 初始化实体类，并将查询结果添加user属性中
				Daily daily = new Daily();
				daily.setId(rSet.getInt("id"));
				daily.setName(rSet.getString("name"));
				daily.setDiary(rSet.getString("diary"));
				daily.setType(rSet.getString("type"));
				daily.setDate(rSet.getTimestamp("date"));
				;
				// 将实体类放在userlist集合中
				dailies.add(daily);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			// 关闭资源
			try {
				if (pStat != null) {
					pStat.close();
				}

				if (conn != null) {
					conn.close();
				}
				if (rSet != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return dailies;

	}
}
